import xlrd
import pandas as pd
import os
import shutil
import re
from glob import glob
from tqdm import tqdm
from pony import orm

from postgres.db_build import MedicineNormalize, ServiceNormalize, ConsumableNormalize, Hospital_coding, \
    Hospital_mapping, HospitalWebData, Country, Hospital, ChineseMedicineNormalize, ServiceVersion
from utils.post_process import post_process, medicine_normalize, service_normalize
from utils.DataProcess import DataProcess
from utils.yibao2medicine import yibao2medicine
from utils.module.medicine_normalization import MedicineNormalizaiton
from utils.module.medical_service_normalization import MedicalServiceNormalization
from utils.module.medical_consumables_normalization import ConsumablesNormalization


class HospitalWebDataProcess(object):
    def __init__(self, dir_path):
        self.dir_path = dir_path
        self.hospital = dir_path.split("/")[-1]
        # 建立目录结构
        if not os.listdir(self.dir_path):
            os.makedirs(os.path.join(self.dir_path, "药品/"))
            os.makedirs(os.path.join(self.dir_path, "医疗服务/"))
            os.makedirs(os.path.join(self.dir_path, "耗材/"))
            shutil.copy("/kv-data/sw/数据清洗/医院网站库模板.xlsx", os.path.join(self.dir_path, "药品/药品.xlsx"))
            shutil.copy("/kv-data/sw/数据清洗/医院网站库模板.xlsx", os.path.join(self.dir_path, "医疗服务/医疗服务.xlsx"))
            shutil.copy("/kv-data/sw/数据清洗/医院网站库模板.xlsx", os.path.join(self.dir_path, "耗材/耗材.xlsx"))
        self.hospital_id = self.get_hospital_coding(self.hospital)
        self.addr = ""
        self.data_process = DataProcess()
        self.consumables_normalization = ConsumablesNormalization()
        # 网站库表头
        self.columns = ["hospital_id", "hospital", "full_name", "customer_name", "standard_name",
                        "nhsa_code", "province_code", "price", "unit", "medical_insurance_type", "ratio",
                        "type",
                        "specification", "manufacturer", "content", "except_content", "remark", "addr"]
        # 添加后处理的pass_flag
        self.columns.insert(self.columns.index("customer_name") + 1, "pass_flag")
        # 后处理自动通过标志
        self.post_pass_flag = 2
        # 添加归一化的pass_flag
        self.columns.insert(self.columns.index("standard_name") + 1, "pass_flag2")
        # 归一化自动通过标志
        self.normalize_pass_flag = 1
        # 后处理人工审核表头
        self.post_process_manual_columns = ["full_name", "customer_name", "pass_flag"]
        # 大项人工审核表头
        self.type_manual_columns = ["full_name", "customer_name", "type"]

        # 输入文件路径
        self.input_file_path = ""
        # 后处理结果
        self.post_process_file_path = "1、后处理.xlsx"
        # 人工处理文件
        self.post_process_manual_file_path = "1、后处理人工审核.xlsx"
        # 人工审核后回填文件
        self.post_process_update_file_path = "2、更新后处理数据.xlsx"
        # 添加大项文件
        self.add_type_file_path = "3、添加大项.xlsx"
        self.add_type_manual_file_path = "3、人工添加大项.xlsx"
        self.add_type_update_file_path = "4、更新大项.xlsx"
        self.output_file_path = "可入库inphile_big_data_web_hospital_data.xlsx"
        # 冗余项
        self.add_parts_file_path = "5、冗余项.xlsx"

        self.menu = str("请输入需要运行的程序：\n"
                        "1、后处理\n"
                        "2、后处理人工审核结果回填\n"
                        "3、添加大项\n"
                        "4、冗余项拆分\n"
                        "5、生成入库文件\n"
                        "输入其他跳过当前流程\n"
                        "回车键确认\n")
        # 通过医院名称搜索医院编码

    # 统一为英文符号
    def ch_en(self, word):
        # 统一为英文符号
        intab = u"！—。【】“”‘’《》—「」『』〖〗〔〕〓︰﹒﹕﹙﹚﹝﹞﹢〈〉∽∶‥–‒Ι×[]（）＃［］{}：％＊﹛﹜，－／÷＿﹡；ⅹ\\"
        outab = u"!-.()\"\"\'\'()-()()()()=:.:()()+()~::--i*()()#()():%*(),-//_*;*/"
        trantab = str.maketrans(intab, outab)  # 一一映射。
        word = word.translate(trantab)  # 返回字符串S的副本，其中每个字符都已通过给定的转换表进行映射。
        # 统一罗马数字
        word = word.replace("Ⅰ", "I")
        word = word.replace("Ⅱ", "II")
        word = word.replace("Ⅲ", "III")
        word = word.replace("Ⅳ", "IV")
        word = word.replace("Ⅴ", "V")
        word = word.replace("Ⅵ", "VI")
        word = word.replace("Ⅶ", "VII")
        word = word.replace("Ⅷ", "VIII")
        word = word.replace("Ⅸ", "IX")
        word = word.replace("Ⅹ", "X")

        if re.findall("＜.*?＞", word):
            word = word.replace("＜", "(")
            word = word.replace("＞", ")")
        if re.findall("<.*?>", word):
            word = word.replace("<", "(")
            word = word.replace(">", ")")
        # 空格处理
        word = re.sub(
            "(?<=[^0-9a-zA-Z])[ ]+(?=[0-9a-zA-Z])|(?<=[0-9a-zA-Z])[ ]+(?=[^0-9a-zA-Z])|"
            "(?<=[^0-9a-zA-Z])[ ]+(?=[^0-9a-zA-Z])|(?<=[()])[ ]+|[ ]+(?=[()\-])",
            "", word)
        # word = re.sub(" ", "", word)
        return word

    @orm.db_session
    def get_hospital_coding(self, hospital):
        hospital_id = ''
        qs = orm.select(
            item
            for item in Hospital_coding
            if
            item.hospital_name_nhsa == hospital
        )
        for item in qs:
            hospital_id = item.hospital_id
            break
        if hospital_id == '':
            qs = orm.select(
                item
                for item in Hospital_mapping
                if
                item.hospital_name_bill == hospital
            )
            for item in qs:
                hospital_id = item.hospital_id
                break
        return hospital_id

    # 输入文件校验
    def input_check(self, columns, path):
        """
        输入校验：
        输入文件表头必须是医院网站库表头的子集，表头命名必须一致
        """
        if set(columns) <= set(self.columns):
            pass
        else:
            raise Exception(f"{os.path.basename(path)}文件表头有误")

    # 后处理审核完成后更新输入文件校验
    def manual_input_check(self, columns, path):
        """
        输入校验：
        输入文件表头必须包含后处理人工审核的所有表头，表头命名必须一致
        """
        if set(columns) >= set(self.post_process_manual_columns):
            pass
        else:
            raise Exception(f"{os.path.basename(path)}文件表头有误")

    # 后处理自动通过
    @orm.db_session()
    def post_auto_pass(self, remark, customer_name, full_name):
        # TODO:存在漏洞，无法确定词条在所有表中模糊匹配可以得到的条数，如果一个词条在一个库中精准匹配，在另一个库存在包含关系，则会误打标签为2
        # TODO:根本上解决需要构建新的数据库，将所有词条包含进去
        # 先匹配原值统一中英文符号后的结果，能匹配上，直接用原值作为甲方回传值
        # 过甲方回传库和归一化标准词条，精准匹配：
        # 能匹配上，再进行包含匹配：
        # 能匹配上且只匹配上一个，后处理值自动通过，标签为2；
        # 匹配到多个，但是原值和甲方值相同，自动通过，标签为2；
        # 匹配到多个，但是原值和甲方值不同，需要人看一下，标签为1；

        # full_name = self.ch_en(full_name)
        if "特需" in full_name:
            return 2, customer_name
        pass_flag = 0
        if remark == "药品":
            qs1 = orm.count(
                medicine
                for medicine in MedicineNormalize
                if full_name in medicine.standard_name
            )
            if qs1 > 0:
                pass_flag = 2
                customer_name = full_name
            else:
                qs1 = MedicineNormalize.select(standard_name=customer_name)
                if qs1.count() > 0:
                    qs1 = orm.count(
                        item.standard_name
                        for item in MedicineNormalize
                        if customer_name in item.standard_name)
                    if qs1 == 1:
                        pass_flag = 2
                    elif qs1 > 1:
                        if customer_name == full_name:
                            pass_flag = 2
                        else:
                            pass_flag = 1
                else:
                    qs2 = ChineseMedicineNormalize.select(standard_name=customer_name)
                    if qs2.count() > 0:
                        qs2 = orm.count(
                            item.standard_name
                            for item in ChineseMedicineNormalize
                            if customer_name in item.standard_name)
                        if qs2 == 1:
                            pass_flag = 2
                        elif qs2 > 1:
                            if customer_name == full_name:
                                pass_flag = 2
                            else:
                                pass_flag = 1
                    else:
                        qs3 = ChineseMedicineNormalize.select(other_name=customer_name)
                        if qs3.count() > 0:
                            qs3 = orm.count(
                                item.other_name
                                for item in ChineseMedicineNormalize
                                if customer_name in item.other_name)
                            if qs3 == 1:
                                pass_flag = 2
                            elif qs3 > 1:
                                if customer_name == full_name:
                                    pass_flag = 2
                                else:
                                    pass_flag = 1
                        else:
                            qs = Country.select(customer_name=customer_name)
                            if qs.count() > 0:
                                qs = orm.count(
                                    item.customer_name
                                    for item in Country
                                    if customer_name in item.customer_name)
                                if qs == 1:
                                    pass_flag = 2
                                elif qs > 1:
                                    if customer_name == full_name:
                                        pass_flag = 2
                                    else:
                                        pass_flag = 1
        elif remark == "医疗服务":
            qs1 = orm.count(
                service
                for service in ServiceNormalize
                if full_name in service.standard_name
            )
            if qs1 > 0:
                pass_flag = 2
                customer_name = full_name
            else:
                qs2 = orm.count(
                    service
                    for service in ServiceVersion
                    if full_name in service.version_name
                )
                if qs2 > 0:
                    pass_flag = 2
                    customer_name = full_name
                else:
                    qs1 = ServiceNormalize.select(standard_name=customer_name)
                    if qs1.count() > 0:
                        qs1 = orm.count(
                            item.standard_name
                            for item in ServiceNormalize
                            if customer_name in item.standard_name)
                        if qs1 == 1:
                            pass_flag = 2
                        elif qs1 > 1:
                            if customer_name == full_name:
                                pass_flag = 2
                            else:
                                pass_flag = 1
                    else:
                        qs2 = ServiceVersion.select(version_name=customer_name)
                        if qs2.count() > 0:
                            qs2 = orm.count(
                                item.standard_name
                                for item in ServiceNormalize
                                if customer_name in item.standard_name)
                            if qs2 == 1:
                                pass_flag = 2
                            elif qs2 > 1:
                                if customer_name == full_name:
                                    pass_flag = 2
                                else:
                                    pass_flag = 1
                            else:
                                pass_flag = 2
                        else:
                            qs = Country.select(customer_name=customer_name)
                            if qs.count() > 0:
                                qs = orm.count(
                                    item.customer_name
                                    for item in Country
                                    if customer_name in item.customer_name)
                                if qs == 1:
                                    pass_flag = 2
                                elif qs > 1:
                                    if customer_name == full_name:
                                        pass_flag = 2
                                    else:
                                        pass_flag = 1
        elif remark == "耗材":
            qs1 = orm.count(
                consumable
                for consumable in ConsumableNormalize
                if full_name in consumable.fourth_level_name
                or full_name in consumable.third_level_name
                or full_name in consumable.common_name
                or full_name in consumable.fourth_keyword_synonym
            )
            if qs1 > 0:
                pass_flag = 2
                customer_name = full_name
            else:
                qs1 = orm.select(
                    consumable
                    for consumable in ConsumableNormalize
                    if consumable.fourth_level_name == customer_name
                    or consumable.third_level_name == customer_name
                    or consumable.common_name == customer_name
                    or consumable.fourth_keyword_synonym == customer_name
                )
                if qs1.count() > 0:
                    pass_flag = 2
                else:
                    qs = Country.select(customer_name=customer_name)
                    if qs.count() > 0:
                        qs = orm.count(
                            item.customer_name
                            for item in Country
                            if customer_name in item.customer_name)
                        if qs == 1:
                            pass_flag = 2
                        elif qs > 1:
                            if customer_name == full_name:
                                pass_flag = 2
                            else:
                                pass_flag = 1

        return pass_flag, customer_name

    # 后处理
    def post_process(self, remark):
        print("开始后处理")
        path = self.input_file_path
        df = pd.read_excel(path, dtype=str, keep_default_na=False)
        # 输入校验
        self.input_check(df.columns.tolist(), path)
        if "charging_basis" in df.columns.tolist():
            df.drop("charging_basis", axis=1)
        # 数据规范去重
        df = df.applymap(lambda y: self.ch_en(y))
        df = df.drop_duplicates()

        table = []
        for index in tqdm(range(0, len(df), 200)):
            cache = []
            input_par = []
            for k in range(0, 200):
                if k + index >= len(df):
                    continue
                # 医院网站库格式
                item = df.iloc[index + k, :]
                series = pd.Series(item, index=self.columns)
                # 医院归一化人工处理
                # series["hospital_id"] = self.hospital_id
                # series["hospital"] = self.hospital
                # 网址人工预填
                # series["addr"] = self.addr
                series["remark"] = remark
                full_name = item["full_name"]
                if full_name:
                    # 后处理
                    input_par.append(
                        {
                            "医院名称": "",
                            "费用名称": full_name,
                            "number": "",
                            "amount": "",
                            "mapping_name": ""
                        })
                    cache.append(series)
                else:
                    table.append(series)

            res = post_process(input_par)
            for k in range(0, len(res)):
                pass_flag = 0
                customer_name = res[k]["mapping_name"]
                full_name = res[k]["name"]
                flag = res[k]["remark"]
                series = cache[k]
                if flag:
                    pass_flag = 2
                else:
                    pass_flag, customer_name = self.post_auto_pass(remark, customer_name, full_name)
                if customer_name:
                    series["customer_name"] = customer_name
                    series["pass_flag"] = pass_flag
                table.append(series)
            if index % 1000 == 0 or (index + 200) > len(df):
                df_output = pd.DataFrame(table)
                df_output = df_output.fillna("")
                df_output.to_excel(self.post_process_file_path, index=False)
                df_check = df_output[df_output["pass_flag"] != self.post_pass_flag][self.post_process_manual_columns]
                df_check.drop_duplicates(inplace=True)
                df_check.to_excel(self.post_process_manual_file_path, index=False)

    # 后处理人工审核后回填
    def update_manual_data_post(self):
        print("开始后处理人工审核值回填")
        manual_path = self.post_process_manual_file_path
        path = self.post_process_file_path
        df = pd.read_excel(path, dtype=str, keep_default_na=False)
        df1 = pd.read_excel(manual_path, dtype=str, keep_default_na=False)
        # 输入校验
        self.manual_input_check(df1.columns.tolist(), manual_path)
        for index, item in tqdm(df1.iterrows(), total=len(df1)):
            df.loc[:, "customer_name"][item["full_name"] == df["full_name"]] = item["customer_name"]
            df.loc[:, "pass_flag"][item["full_name"] == df["full_name"]] = self.post_pass_flag
        df.to_excel(self.post_process_update_file_path, index=False)

    # 添加大项
    @orm.db_session
    def add_type(self):
        print("开始添加大项")
        path = self.post_process_update_file_path
        df = pd.read_excel(path, dtype=str, keep_default_na=False)
        df_output = df.copy()
        for index, item in tqdm(df_output.iterrows(), total=len(df_output)):
            if item["remark"] == "耗材":
                item["type"] = "卫生材料费"
            if not item["type"]:
                customer_name = item["customer_name"]
                writed = 0
                if item["remark"] == "药品":
                    qs1 = orm.select(
                        medicine.nhsa_code
                        for medicine in MedicineNormalize
                        if medicine.standard_name == customer_name
                        or medicine.other_name == customer_name
                    )
                    if qs1.count() > 0:
                        for it in qs1:
                            if len(it) > 0:
                                if it[0] == "X":
                                    _type = "西药费"
                                    item["type"] = _type
                                    writed = 1
                                    break
                                elif it[0] == "Z":
                                    _type = "中成药费"
                                    item["type"] = _type
                                    writed = 1
                                    break
                    if not writed:
                        qs2 = orm.count(
                            CM
                            for CM in ChineseMedicineNormalize
                            if CM.standard_name == customer_name
                            or CM.other_name == customer_name
                        )
                        if qs2 > 0:
                            _type = "中药饮片"
                            item["type"] = _type
                            writed = 1
                if not writed:
                    qs = orm.select(
                        details.type
                        for details in Country
                        if details.customer_name == customer_name
                        and details.type
                    )
                    if qs.count() > 0:
                        for it in qs:
                            if len(it) > 2:
                                _type = it
                                item["type"] = _type
                                break

        df_output.to_excel(self.add_type_file_path, index=False)
        df_check = df_output[df_output["type"] == ""][self.type_manual_columns]
        df_check.drop_duplicates(inplace=True)
        df_check.to_excel(self.add_type_manual_file_path, index=False)

    # 生成入库文件
    def output(self):
        print("生成入库文件中...")
        manual_path = self.add_type_manual_file_path
        path = self.add_type_file_path
        df = pd.read_excel(path, dtype=str, keep_default_na=False)
        df1 = pd.read_excel(manual_path, dtype=str, keep_default_na=False)
        for index, item in tqdm(df1.iterrows(), total=len(df1)):
            if item["type"]:
                df.loc[:, "type"][item["full_name"] == df["full_name"]] = item["type"]
        df.to_excel(self.add_type_update_file_path, index=False)
        if ((df["pass_flag"] != "2") & (df["pass_flag"] != 2)).any():
            print("后处理中存在未通过的条目，请检查pass_flag")
        if (df["type"] == "").any():
            print("大项存在空值，需要人工填充")
        shutil.copy(self.add_type_update_file_path, self.output_file_path)

    # 计算冗余项
    def add_parts(self):
        print("开始计算冗余项")
        path = self.post_process_update_file_path
        df = pd.read_excel(path, dtype=str, keep_default_na=False)
        table_parts = []
        for index, series in tqdm(df.iterrows(), total=len(df)):
            if series["full_name"]:
                series_parts = pd.Series(series, index=["full_name", "customer_name",
                                                        "sub_name1", "sub_name2", "sub_name3",
                                                        "specification",
                                                        "manufacturer",
                                                        "special_symbol", "others"])

                standard_name = series_parts["customer_name"]
                other_name = series_parts["full_name"]
                other_name = self.data_process.ch_en(other_name)
                if standard_name:
                    cust_name, parts = self.data_process.split_parts(other_name)
                    other_parts = self.data_process.deleted_words(other_name, standard_name)
                    if parts:
                        for k, v in parts.items():
                            if k != "其他":
                                for i in v.split(" "):
                                    if i and i in other_parts:
                                        other_parts = other_parts.replace(i, "")
                                        other_parts = other_parts.strip()

                        if not re.search("[0-9a-zA-Z\u4e00-\u9fa5]", other_parts):
                            other_parts = ""
                        if other_parts != parts["其他"]:
                            parts["其他"] += self.data_process.deleted_words(other_parts, parts["其他"])
                        for i, part in enumerate(parts["医用术语"].split(" ")):
                            if i < 3:
                                series_parts[f"sub_name{i + 1}"] = part
                        if not series_parts["specification"]:
                            series_parts["specification"] += parts["规格"]
                        if not series_parts["manufacturer"]:
                            series_parts["manufacturer"] = parts["药厂"]
                        series_parts["special_symbol"] = parts["特殊符号"]
                        series_parts["others"] = parts["其他"]
                table_parts.append(series_parts)
        df_output = pd.DataFrame(table_parts, dtype=str)
        df_output = df_output.fillna("")
        df_output.to_excel(self.add_parts_file_path, index=False)


# 药品入库
class HospitalWebDataProcessMedicine(HospitalWebDataProcess):
    def __init__(self, dir_path):
        super(HospitalWebDataProcessMedicine, self).__init__(dir_path)
        self.medicine_normalization = MedicineNormalizaiton()
        # 药品归一化表头
        self.medicine_normalize_columns = ["base_code", "permission_code", "nhsa_code", "standard_name",
                                           "standard_principal_component", "standard_formulation", "other_name",
                                           "trade_name", "principal_component", "formulation", "sub_name1",
                                           "sub_name2", "sub_name3", "specification", "manufacturer", "special_symbol",
                                           "others"]
        self.dir_path = os.path.join(self.dir_path, "药品")
        self.input_file_path = os.path.join(self.dir_path, "药品.xlsx")
        self.post_process_file_path = os.path.join(self.dir_path, self.post_process_file_path)
        self.post_process_manual_file_path = os.path.join(self.dir_path, self.post_process_manual_file_path)
        self.post_process_update_file_path = os.path.join(self.dir_path, self.post_process_update_file_path)
        self.normalize_file_path = os.path.join(self.dir_path, "药inphile_big_data_web_hospital_data.xlsx")
        self.normalize_manual_file_path = os.path.join(self.dir_path, "归一化人工审核.xlsx")
        self.normalize_update_file_path = os.path.join(self.dir_path, "更新药inphile_big_data_web_hospital_data.xlsx")
        # 添加大项文件
        self.add_type_file_path = os.path.join(self.dir_path, self.add_type_file_path)
        self.add_type_manual_file_path = os.path.join(self.dir_path, self.add_type_manual_file_path)
        self.add_type_update_file_path = os.path.join(self.dir_path, self.add_type_update_file_path)
        self.output_file_path = os.path.join(self.dir_path, self.output_file_path)
        # 冗余项
        self.add_parts_file_path = os.path.join(self.dir_path, self.add_parts_file_path)

    # 后处理
    def medicine_post_process(self, remark="药品"):
        if os.path.exists(self.input_file_path):
            self.post_process(remark)
        else:
            print("药品输入文件不存在或命名错误")

    @orm.db_session()
    def medicine_normal_process(self):
        # 人工审核后的后处理结果
        path = self.post_process_update_file_path
        # 如果人工审核数据未回填，使用原后处理结果跑归一化
        if not os.path.exists(path):
            path = self.post_process_file_path
        df = pd.read_excel(path, dtype=str, keep_default_na=False)

        table = []
        for index in tqdm(range(0, len(df))):
            # 医院网站库格式
            item = df.iloc[index, :]
            series = pd.Series(item, index=self.columns)

            full_name = item["full_name"]
            pass_flag = item["pass_flag"]
            # 后处理自动通过的，用于后续归一化，不自动通过的，归一化用原值
            if pass_flag != "0":
                customer_name = item["customer_name"]
            else:
                customer_name = full_name

            if customer_name:
                # 归一化，没有标准词条
                if not series["nhsa_code"]:
                    # try:
                    #     standard_name = medicine_normalize(customer_name)
                    # except:
                    #     standard_name = ""
                    # if standard_name:
                    #     # 当返回候选值时，如果第一推荐值包含于原值，则认为数据可信
                    #     pass_flag2 = 1
                    #     if type(standard_name) != str:
                    #         if standard_name[0][0]["候选项"] in full_name:
                    #             standard_name = standard_name[0][0]["候选项"]
                    #         else:
                    #             standard_name = standard_name[0][0]["候选项"]
                    #             pass_flag2 = 0
                    #     # 当得到可信数据或者返回值自动通过时，数据入库
                    #     if type(standard_name) == str and standard_name:
                    #         series["standard_name"] = standard_name
                    #         qs = MedicineNormalize.select(standard_name=standard_name)
                    #         if len(qs) > 0:
                    #             for it in qs:
                    #                 nhsa_code = it.nhsa_code
                    #                 series["nhsa_code"] = nhsa_code
                    #                 series["pass_flag2"] = pass_flag2
                    #                 break
                    # else:
                    #     series["pass_flag2"] = 0

                    standard_name, _ = self.medicine_normalization.medicine_accurate_match(full_name, customer_name)
                    if _:
                        standard_name = standard_name[0]
                        series["standard_name"] = standard_name
                        series["pass_flag2"] = 1
                        qs = MedicineNormalize.select(standard_name=standard_name)
                        if len(qs) > 0:
                            for it in qs:
                                nhsa_code = it.nhsa_code
                                series["nhsa_code"] = nhsa_code
                                break
                    else:
                        standard_name, _ = self.medicine_normalization.normalize(customer_name)
                        pass_flag2 = 0
                        if type(standard_name[0]) == dict:
                            standard_name = standard_name[0]["name"]
                        else:
                            standard_name = standard_name[0]
                        # 当返回候选值时，如果第一推荐值包含于原值，则认为数据可信
                        if standard_name in full_name:
                            pass_flag2 = 1
                            qs = MedicineNormalize.select(standard_name=standard_name)
                            if len(qs) > 0:
                                for it in qs:
                                    nhsa_code = it.nhsa_code
                                    series["nhsa_code"] = nhsa_code
                                    break
                        # 当得到可信数据或者返回值自动通过时，数据入库
                        if type(standard_name) == str and standard_name:
                            series["standard_name"] = standard_name
                            series["pass_flag2"] = pass_flag2

                # 已经带有医保编码时
                else:
                    nhsa_code = series["nhsa_code"]
                    qs = MedicineNormalize.select(nhsa_code=nhsa_code[:-9])
                    if len(qs) > 0:
                        for it in qs:
                            standard_name = it.standard_name
                            series["standard_name"] = standard_name
                            series["pass_flag2"] = 1
                            break
            table.append(series)
            if index % 500 == 0 or index == len(df) - 1:
                df_output = pd.DataFrame(table)
                df_output = df_output.fillna("")
                df_output.to_excel(self.normalize_file_path, index=False)
                df_check = df_output[df_output["pass_flag2"] != self.normalize_pass_flag][
                    ["full_name", "standard_name", "pass_flag2"]]
                df_check.drop_duplicates(inplace=True)
                df_check.to_excel(self.normalize_manual_file_path, index=False)

    # 归一化人工审核后回填
    def update_manual_data_normal(self):
        manual_path = os.path.join(self.dir_path, "归一化人工审核.xlsx")
        path = os.path.join(self.dir_path, "药inphile_big_data_web_hospital_data.xlsx")
        df = pd.read_excel(path, dtype=str, keep_default_na=False)
        df1 = pd.read_excel(manual_path, dtype=str, keep_default_na=False)
        for index, item in tqdm(df1.iterrows(), total=len(df1)):
            df.loc[:, "standard_name"][item["full_name"] == df["full_name"]] = item["standard_name"]
            df.loc[:, "pass_flag2"][item["full_name"] == df["full_name"]] = 1
        df.to_excel(path.replace("药inp", "更新药inp"), index=False)

    @orm.db_session()
    def medicine_normalize_data_base(self, path):
        df = pd.read_excel(path, dtype=str, keep_default_na=False)
        table_gy = []
        for index in tqdm(range(0, len(df))):
            # 医院网站库格式
            item = df.iloc[index, :]
            series = pd.Series(item, index=self.medicine_columns)

            full_name = item["full_name"]
            standard_name = series["standard_name"]
            # 入归一化库
            if type(full_name) != str:
                full_name = str(full_name)
            qs = MedicineNormalize.select(other_name=full_name)
            if len(qs) == 0:
                if type(series["standard_name"]) == str:
                    series_gy = pd.Series(series, index=self.medicine_normalize_columns)
                    qs = MedicineNormalize.select(standard_name=standard_name)
                    if len(qs) > 0:
                        for it in qs:
                            series_gy["base_code"] = it.base_code
                            series_gy["permission_code"] = it.permission_code
                            series_gy["standard_principal_component"] = it.standard_principal_component
                            series_gy["standard_formulation"] = it.standard_formulation

                            series_gy["principal_component"] = it.principal_component
                            series_gy["formulation"] = it.formulation

                            break
                        for it in qs:
                            if it.trade_name and it.trade_name in full_name:
                                series_gy["trade_name"] = it.trade_name
                    series_gy["other_name"] = full_name

                    standard_name = series_gy["standard_name"]
                    other_name = series_gy["other_name"]
                    trade_name = series_gy["trade_name"]
                    other_name = self.data_process.ch_en(other_name)
                    if standard_name:
                        cust_name, parts = self.data_process.split_parts(other_name)
                        other_parts = self.data_process.deleted_words(other_name, standard_name)
                        if type(trade_name) == str and trade_name in other_parts:
                            other_parts = other_parts.replace(trade_name, "")
                        if parts:
                            for k, v in parts.items():
                                if k != "其他":
                                    for i in v.split(" "):
                                        if i and i in other_parts:
                                            other_parts = other_parts.replace(i, "")
                                            other_parts = other_parts.strip()

                            if not re.search("[0-9a-zA-Z\u4e00-\u9fa5]", other_parts):
                                other_parts = ""
                            if other_parts != parts["其他"]:
                                parts["其他"] += self.data_process.deleted_words(other_parts, parts["其他"])
                            for i, part in enumerate(parts["医用术语"].split(" ")):
                                if i < 3:
                                    series_gy[f"sub_name{i + 1}"] = part
                            series_gy["specification"] += parts["规格"]
                            # series_gy["manufacturer"] = parts["药厂"]
                            series_gy["special_symbol"] = parts["特殊符号"]
                            series_gy["others"] = parts["其他"]
                        table_gy.append(series_gy)
            if index % 500 == 0 or index == len(df) - 1:
                df_output = pd.DataFrame(table_gy)
                df_output = df_output.fillna("")
                df_output.to_excel(
                    path.replace(os.path.basename(path), "inphile_big_data_medicine_normalize.xlsx"), index=False)

    # 主入口
    def main(self):
        print("药品处理：")
        if not os.path.exists(self.input_file_path):
            print("药品输入文件不存在或命名错误")
            return 0
        key = input(self.menu)
        if "1" in key:
            self.medicine_post_process()
        if "2" in key:
            self.update_manual_data_post()
        if "3" in key:
            self.add_type()
        if "4" in key:
            self.add_parts()
        if "5" in key:
            self.output()


# 服务入库
class HospitalWebDataProcessService(HospitalWebDataProcess):
    def __init__(self, dir_path):
        super(HospitalWebDataProcessService, self).__init__(dir_path)
        self.medical_sevice_normalization = MedicalServiceNormalization()
        self.dir_path = os.path.join(self.dir_path, "医疗服务")
        self.input_file_path = os.path.join(self.dir_path, "医疗服务.xlsx")
        self.post_process_file_path = os.path.join(self.dir_path, self.post_process_file_path)
        self.post_process_manual_file_path = os.path.join(self.dir_path, self.post_process_manual_file_path)
        self.post_process_update_file_path = os.path.join(self.dir_path, self.post_process_update_file_path)
        self.normalize_file_path = os.path.join(self.dir_path, "药inphile_big_data_web_hospital_data.xlsx")
        self.normalize_manual_file_path = os.path.join(self.dir_path, "归一化人工审核.xlsx")
        self.normalize_update_file_path = os.path.join(self.dir_path, "更新药inphile_big_data_web_hospital_data.xlsx")
        # 添加大项文件
        self.add_type_file_path = os.path.join(self.dir_path, self.add_type_file_path)
        self.add_type_manual_file_path = os.path.join(self.dir_path, self.add_type_manual_file_path)
        self.add_type_update_file_path = os.path.join(self.dir_path, self.add_type_update_file_path)
        self.output_file_path = os.path.join(self.dir_path, self.output_file_path)
        # 冗余项
        self.add_parts_file_path = os.path.join(self.dir_path, self.add_parts_file_path)

    @orm.db_session()
    def service_post_process(self, remark="医疗服务"):
        if os.path.exists(self.input_file_path):
            self.post_process(remark)
        else:
            print("医疗服务输入文件不存在或命名错误")

    @orm.db_session()
    def service_normal_process_old(self):
        path = self.input_file_path
        df = pd.read_excel(path, dtype=str, keep_default_na=False)
        # 输入校验
        self.input_check(df.columns.tolist(), path)
        if "charging_basis" in df.columns.tolist():
            df.drop("charging_basis", axis=1)
        table = []
        for index in tqdm(range(0, len(df))):
            # 入医院网站库
            item = df.iloc[index, :]
            series = pd.Series(item, index=self.columns)

            series["hospital_id"] = self.hospital_id
            series["hospital"] = self.hospital
            series["addr"] = self.addr
            series["type"] = "服务"
            full_name = item["full_name"]

            # 后处理
            input = [
                {
                    "医院名称": "",
                    "费用名称": full_name,
                    "number": "",
                    "amount": "",
                    "mapping_name": ""
                }
            ]
            customer_name = post_process(input)
            if customer_name:
                series["customer_name"] = customer_name

                # 归一化
                if not series["nhsa_code"]:
                    try:
                        standard_name = service_normalize(customer_name)
                    except:
                        standard_name = ""
                    if standard_name:
                        if type(standard_name) != str:
                            if standard_name[0][0]["候选项"] in full_name:
                                standard_name = standard_name[0][0]["候选项"]
                        if type(standard_name) == str and standard_name:
                            series["standard_name"] = standard_name
                            qs = ServiceNormalize.select(standard_name=standard_name)
                            if len(qs) > 0:
                                for it in qs:
                                    nhsa_code = it.nhsa_code
                                    series["nhsa_code"] = nhsa_code
                                    break
                # 已经带有医保编码时
                else:
                    nhsa_code = series["nhsa_code"]
                    qs = ServiceNormalize.select(nhsa_code=nhsa_code)
                    if len(qs) > 0:
                        for it in qs:
                            standard_name = it.standard_name
                            series["standard_name"] = standard_name
                            break
            table.append(series)
            # 入归一化库
            qs = ServiceNormalize.select(other_name=full_name)
            if len(qs) == 0:
                if series["standard_name"]:
                    series_gy = pd.Series(series, index=["nhsa_code", "standard_name",
                                                         "other_name", "sub_name1", "sub_name2", "sub_name3",
                                                         "specification",
                                                         "manufacturer",
                                                         "special_symbol", "others"])

                    series_gy["other_name"] = full_name
                    series_gy["specification"] = series["specification"]

                    standard_name = series_gy["standard_name"]
                    other_name = series_gy["other_name"]
                    other_name = self.data_process.ch_en(other_name)
                    if standard_name:
                        cust_name, parts = self.data_process.split_parts(other_name)
                        other_parts = self.data_process.deleted_words(other_name, standard_name)
                        if parts:
                            for k, v in parts.items():
                                if k != "其他":
                                    for i in v.split(" "):
                                        if i and i in other_parts:
                                            other_parts = other_parts.replace(i, "")
                                            other_parts = other_parts.strip()

                            if not re.search("[0-9a-zA-Z\u4e00-\u9fa5]", other_parts):
                                other_parts = ""
                            if other_parts != parts["其他"]:
                                parts["其他"] += self.data_process.deleted_words(other_parts, parts["其他"])
                            for i, part in enumerate(parts["医用术语"].split(" ")):
                                if i < 3:
                                    series_gy[f"sub_name{i + 1}"] = part
                            # series_gy["specification"] += parts["规格"]
                            # series_gy["manufacturer"] = parts["药厂"]
                            series_gy["special_symbol"] = parts["特殊符号"]
                            series_gy["others"] = parts["其他"]
                        table_gy.append(series_gy)
            if index % 500 == 0 or index == len(df) - 1:
                df_output = pd.DataFrame(table)
                df_output = df_output.fillna("")
                df_output.to_excel(path.replace(os.path.basename(path), "服务inphile_big_data_web_hospital_data.xlsx"))
                df_output = pd.DataFrame(table_gy)
                df_output = df_output.fillna("")
                df_output.to_excel(
                    path.replace(os.path.basename(path), "0718_new_inphile_big_data_service_normalize.xlsx"))

    # 主入口
    def main(self):
        print("医疗服务处理：")
        if not os.path.exists(self.input_file_path):
            print("医疗服务输入文件不存在或命名错误")
            return 0
        key = input(self.menu)
        if "1" in key:
            self.service_post_process()
        if "2" in key:
            self.update_manual_data_post()
        if "3" in key:
            self.add_type()
        if "4" in key:
            self.add_parts()
        if "5" in key:
            self.output()


# 耗材入库
class HospitalWebDataProcessConsumable(HospitalWebDataProcess):
    def __init__(self, dir_path):
        super(HospitalWebDataProcessConsumable, self).__init__(dir_path)
        self.medical_sevice_normalization = MedicalServiceNormalization()
        self.dir_path = os.path.join(self.dir_path, "耗材")
        self.input_file_path = os.path.join(self.dir_path, "耗材.xlsx")
        self.post_process_file_path = os.path.join(self.dir_path, self.post_process_file_path)
        self.post_process_manual_file_path = os.path.join(self.dir_path, self.post_process_manual_file_path)
        self.post_process_update_file_path = os.path.join(self.dir_path, self.post_process_update_file_path)
        self.normalize_file_path = os.path.join(self.dir_path, "药inphile_big_data_web_hospital_data.xlsx")
        self.normalize_manual_file_path = os.path.join(self.dir_path, "归一化人工审核.xlsx")
        self.normalize_update_file_path = os.path.join(self.dir_path, "更新药inphile_big_data_web_hospital_data.xlsx")
        # 添加大项文件
        self.add_type_file_path = os.path.join(self.dir_path, self.add_type_file_path)
        self.add_type_manual_file_path = os.path.join(self.dir_path, self.add_type_manual_file_path)
        self.add_type_update_file_path = os.path.join(self.dir_path, self.add_type_update_file_path)
        self.output_file_path = os.path.join(self.dir_path, self.output_file_path)
        # 冗余项
        self.add_parts_file_path = os.path.join(self.dir_path, self.add_parts_file_path)

    @orm.db_session()
    def consumable_post_process(self, remark="耗材"):
        if os.path.exists(self.input_file_path):
            self.post_process(remark)
        else:
            print("耗材输入文件不存在或命名错误")

    # 主入口
    def main(self):
        print("耗材处理：")
        if not os.path.exists(self.input_file_path):
            print("耗材输入文件不存在或命名错误")
            return 0
        key = input(self.menu)
        if "1" in key:
            self.consumable_post_process()
        if "2" in key:
            self.update_manual_data_post()
        if "3" in key:
            self.add_type()
        if "4" in key:
            self.add_parts()
        if "5" in key:
            self.output()

    @orm.db_session()
    def consumable_process(self, path):
        df = pd.read_excel(path, dtype=str, keep_default_na=False)
        if "charging_basis" in df.columns.tolist():
            df.drop("charging_basis", axis=1)
        table = []
        table_gy = []
        for index in tqdm(range(0, len(df))):
            # 入医院网站库
            item = df.iloc[index, :]
            series = pd.Series(item, index=["hospital_id", "hospital", "full_name", "customer_name", "standard_name",
                                            "nhsa_code", "province_code", "price", "unit", "medical_insurance_type",
                                            "ratio", "type", "specification", "manufacturer", "content",
                                            "except_content",
                                            "remark", "addr"])

            series["hospital_id"] = self.hospital_id
            series["hospital"] = self.hospital
            series["addr"] = self.addr
            series["type"] = "耗材"
            full_name = item["full_name"]

            # 后处理
            input = [
                {
                    "医院名称": "",
                    "费用名称": full_name,
                    "number": "",
                    "amount": "",
                    "mapping_name": ""
                }
            ]
            customer_name = post_process(input)
            if customer_name:
                series["customer_name"] = customer_name

                # 归一化
                if not series["nhsa_code"]:
                    try:
                        standard_name = service_normalize(customer_name)
                    except:
                        standard_name = ""
                    if standard_name:
                        if type(standard_name) != str:
                            if standard_name[0][0]["候选项"] in full_name:
                                standard_name = standard_name[0][0]["候选项"]
                        if type(standard_name) == str and standard_name:
                            series["standard_name"] = standard_name
                            qs = ServiceNormalize.select(standard_name=standard_name)
                            if len(qs) > 0:
                                for it in qs:
                                    nhsa_code = it.nhsa_code
                                    series["nhsa_code"] = nhsa_code
                                    break
                # 已经带有医保编码时
                else:
                    # 耗材归一化值留空
                    nhsa_code = series["nhsa_code"][:20]
                    series["nhsa_code"] = nhsa_code[:-5]
                    # qs = ConsumableNormalize.select(nhsa_code=nhsa_code)
                    # if len(qs) > 0:
                    #     for it in qs:
                    #         nhsa_name = it.nhsa_name
                    #         series["nhsa_name"] = nhsa_name
                    #         break
            table.append(series)
            # 入归一化库
            qs = ConsumableNormalize.select(other_name=full_name)
            if len(qs) == 0:
                if series["standard_name"]:
                    series_gy = pd.Series(series, index=["nhsa_code", "standard_name",
                                                         "other_name", "sub_name1", "sub_name2", "sub_name3",
                                                         "specification",
                                                         "manufacturer",
                                                         "special_symbol", "others"])

                    series_gy["other_name"] = full_name
                    series_gy["specification"] = series["unit"]

                    standard_name = series_gy["standard_name"]
                    other_name = series_gy["other_name"]
                    other_name = self.data_process.ch_en(other_name)
                    if standard_name:
                        cust_name, parts = self.data_process.split_parts(other_name)
                        other_parts = self.data_process.deleted_words(other_name, standard_name)
                        if parts:
                            for k, v in parts.items():
                                if k != "其他":
                                    for i in v.split(" "):
                                        if i and i in other_parts:
                                            other_parts = other_parts.replace(i, "")
                                            other_parts = other_parts.strip()

                            if not re.search("[0-9a-zA-Z\u4e00-\u9fa5]", other_parts):
                                other_parts = ""
                            if other_parts != parts["其他"]:
                                parts["其他"] += self.data_process.deleted_words(other_parts, parts["其他"])
                            for i, part in enumerate(parts["医用术语"].split(" ")):
                                if i < 3:
                                    series_gy[f"sub_name{i + 1}"] = part
                            # series_gy["specification"] += parts["规格"]
                            # series_gy["manufacturer"] = parts["药厂"]
                            series_gy["special_symbol"] = parts["特殊符号"]
                            series_gy["others"] = parts["其他"]
                        table_gy.append(series_gy)
            if index % 500 == 0 or index == len(df) - 1:
                df_output = pd.DataFrame(table)
                df_output = df_output.fillna("")
                df_output.to_excel(path.replace(os.path.basename(path), "耗材inphile_big_data_web_hospital_data1.xlsx"))
                df_output = pd.DataFrame(table_gy)
                df_output = df_output.fillna("")
                df_output.to_excel(
                    path.replace(os.path.basename(path), "0717_new_inphile_big_data_consumable_normalize1.xlsx"))

    # 数据库中添加大项
    @orm.db_session()
    def add_type_database(self):
        # 用甲方值添加
        # qs = orm.select(
        #     (web_data, country.type)
        #     for web_data in HospitalWebData for country in Country
        #     if not web_data.type
        #     and web_data.customer_name == country.customer_name
        #     and country.type
        # )
        # if qs.count() >0:
        #     count = 0
        #     for web_data, country_type in tqdm(qs, total=qs.count()):
        #         web_data.type = country_type
        #         print(web_data.full_name, web_data.type)
        #         count += 1
        #     if count % 1000 == 0:
        #         orm.commit()

        # 用归一化值添加
        qs = orm.select(
            (web_data, details.type)
            for web_data in HospitalWebData for details in Hospital
            if not web_data.type
            and web_data.nhsa_name
            and web_data.nhsa_name == details.nhsa_name
            and details.type
        )
        if qs.count() > 0:
            count = 0
            for web_data, country_type in tqdm(qs, total=qs.count()):
                web_data.type = country_type
                print(web_data.full_name, web_data.type)
                count += 1
            if count % 1000 == 0:
                orm.commit()

    # def main(self):
    #     file_dir = "/home/kv/workspace/归一化库/北京大学第一医院_beijing/北京大学第一医院"
    #     files = glob(file_dir + "/*.xlsx")
    #     # for path in files:
    #     #     if "药品" in path:
    #     #         self.medicine_process(path)
    #     for path in files:
    #         if "服务" in path:
    #             self.service_process(path)
    # for path in files:
    #     if "材料" in path:
    #         self.consumable_process(path)


if __name__ == '__main__':
    dirs = []
    dir_path = "/media/kv/_dde_data/workspace/数据清晰/data_process_code/hospital_web_data_process/医院网站/北京市/北京大学第六医院"
    # 药品
    M = HospitalWebDataProcessMedicine(dir_path)

    # 医疗服务
    S = HospitalWebDataProcessService(dir_path)

    # 耗材
    C = HospitalWebDataProcessConsumable(dir_path)
    # 后处理
    M.main()
    S.main()
    C.main()

    # 后处理人工处理更新
    # hospital_web_data_process.update_manual_data_post()
    # 归一化
    # hospital_web_data_process.medicine_normal_process()
    # 归一化人工处理更新
    # hospital_web_data_process.update_manual_data_normal()
    # hospital_web_data_process.medicine_normalize_data_base(
    #     "/home/kv/workspace/归一化库/浙江医院/药inphile_big_data_web_hospital_data.xlsx")
    # yibao2medicine("/kv-data/sw/查药扣费/数据/51-西宁市/1-2西药部分.xlsx")
